package org.gpf.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.gpf.dao.IUserDAO;
import org.gpf.vo.User;

/**
 * 真实主题角色
 * @author gaopengfei
 * @date 2015-4-23 下午8:42:32
 */
public class UserDAOImpl implements IUserDAO {

	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	private String sql;
	boolean flag = false;
	
	public UserDAOImpl(Connection conn) {

		this.conn = conn;
	}
	
	@Override
	public boolean doCreate(User user) {
		
		sql = "INSERT INTO user(username,password,sex,age,birthday) VALUES (?,?,?,?,?)";
		flag = false;
		
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getSex());
			pstmt.setInt(4, user.getAge());
			pstmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));
			
			if (pstmt.executeUpdate() > 0)
				flag = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doUpdate(User user) {

		flag = false;
		sql = "UPDATE user SET username = ?,password = ?,sex = ?,age = ?,birthday=? WHERE id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getSex());
			pstmt.setInt(4, user.getAge());
			pstmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));
			pstmt.setInt(6, user.getId());
			
			if (pstmt.executeUpdate() > 0)
				flag = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int id) {

		flag = false;
		sql = "DELETE FROM user WHERE id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			if (pstmt.executeUpdate() > 0)
				flag = true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return flag;
		
	}

	@Override
	public User findById(int id) {
		
		User user = null;
		sql = "SELECT id,username,password,sex,age,birthday FROM user WHERE id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setSex(rs.getString(4));
				user.setAge(rs.getInt(5));
				user.setBirthday(rs.getDate(6));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return user;

	}

	@Override
	public List<User> findAll(String keyword) {

		List<User>all = new ArrayList<User>();
		User user;
		
		/**
		 * 很奇葩的MySQL中文关键字查询出现的Illegal mix of collations for operation 'like'
		 * 在 select 时若使用 like '%中文%' 会出现 Illegal mix of collations for operation 'like'
		 * 这在比较久的版本MySQL是不会出现错误的。但是升级到MySQL 5.5以上，必需改成like binary '%中文%' 即可避免出现错误
		 */
		
		sql = "SELECT id,username,password,sex,age,birthday FROM user WHERE username LIKE BINARY ? OR password LIKE BINARY ? OR sex LIKE BINARY ? OR age LIKE BINARY ? OR birthday LIKE BINARY ? ";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");
			pstmt.setString(4, "%" + keyword + "%");
			pstmt.setString(5, "%" + keyword + "%");
			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setUsername(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setSex(rs.getString(4));
				user.setAge(rs.getInt(5));
				user.setBirthday(rs.getDate(6));
				all.add(user);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return all;
	}

	@Override
	public boolean findLogin(User user) {

		boolean flag = false;
		sql = "SELECT username FROM user WHERE username = ? AND password = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getPassword());
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean isUsernameExists(String username) {
		
		boolean flag = false;
		
		sql = "SELECT COUNT(username) FROM user WHERE username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, username);
			rs = pstmt.executeQuery();
			
			/* 查询相同的用户名的数量 */
			if (rs.next() && rs.getInt(1) > 0)
				flag = true;
			else
				flag = false;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}

}
